package org.example.pack1210.dao;

import lombok.SneakyThrows;
import org.example.pack1210.bean.Dept;
import org.example.pack1210.bean.Emp;
import org.example.pack1210.db.DBManager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Emp3DAO {

    private Connection conn;
    private PreparedStatement ps;
    private ResultSet rs;

    @SneakyThrows
    public Emp findById(int id) {
        conn = DBManager.getConnection2();
        String sql = "select * from emp e left join DEPT D on e.deptno = D.DEPTNO where state=1 and empno=?";
        ps = conn.prepareStatement(sql);
        ps.setInt(1, id);
        rs = ps.executeQuery();
        if (rs.next()) {
            // 将前9列的值赋予Emp的对象
            Emp emp = new Emp();
            int empNo = rs.getInt(1);
            String enname = rs.getString(2);
            String job = rs.getString("job");
            int mgr = rs.getInt(4);
            String hireDate = rs.getString("hireDate");
            double sal = rs.getDouble("sal");
            double comm = rs.getDouble("comm");
            int deptNo = rs.getInt(8);
            int state = rs.getInt(9);
            // 封装
            emp.setEmpNo(empNo);
            emp.setComm(comm);
            emp.setJob(job);
            emp.setEnname(enname);
            emp.setHiredate(hireDate);
            emp.setDeptNo(deptNo);
            emp.setMgr(mgr);
            emp.setState(state);
            emp.setSal(sal);
            // 将10、11、12列的值赋予dept对象
            Dept dept = new Dept();
            dept.setDeptNo(rs.getInt(10));
            dept.setDname(rs.getString(11));
            dept.setLoc(rs.getString(12));
            // 封装
            emp.setDept(dept);
            return emp;
        }

        return null;
    }
}
